﻿/******************************************************************************
'Description:装试生产进度看板类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhunan
'create Date:2019.7.26
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class ZSWorkingProcessLookBoardBusiness
    {
        private uMESReportOracleDAL dal = new uMESReportOracleDAL();

        //查询年生产任务完成进度
        public DataTable GetZsYearWorkingProcess()
        {
            //批产、大修、备件年计划数
            string sql = @"
SELECT nvl(yp.batchproduct,0) batchproduct,nvl(yp.bigrepair,0) bigrepair,nvl(yp.spareparts,0) spareparts,yp.startdate,yp.finishdate
FROM zsyearplanproduce yp WHERE yp.planyear = to_char(SYSDATE,'yyyy')
";
            DataTable dtYearPlan = dal.GetDataTable(sql);

            //新机、检修年计划数(注:订单分厂结束时间在本年)
            sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,mfg.factorycompletiondate,
       CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
            ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                       WHEN ot.ordertypename = 'PD02' THEN '新机'
                       WHEN ot.ordertypename = 'PD03' THEN '新机'
                       WHEN ot.ordertypename = 'PD05' THEN '大修'
                       WHEN ot.ordertypename = 'PD06' THEN '检修'
                  END) 
       END ordertypename
FROM mfgorder mfg
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
";
            DataTable dtNewMachinePlan = dal.GetDataTable(sql);

            DataTable dtPlan = new DataTable();

            dtPlan.Columns.Add("batchproduct", typeof(Int32));//批产
            dtPlan.Columns.Add("spareparts", typeof(Int32));//备件
            dtPlan.Columns.Add("bigrepair", typeof(Int32));//大修
            dtPlan.Columns.Add("newmachine", typeof(Int32));//新机
            dtPlan.Columns.Add("examerepair", typeof(Int32));//检修
            dtPlan.Columns.Add("startdate");//分厂计划开始时间
            dtPlan.Columns.Add("finishdate");//分厂计划完成时间

            DataRow datarow = dtPlan.NewRow();
            datarow["batchproduct"] = dtYearPlan.Rows.Count > 0 ? dtYearPlan.Rows[0]["batchproduct"] : 0;
            datarow["spareparts"] = dtYearPlan.Rows.Count > 0 ? dtYearPlan.Rows[0]["spareparts"] : 0;
            datarow["bigrepair"] = dtYearPlan.Rows.Count > 0 ? dtYearPlan.Rows[0]["bigrepair"] : 0;
            datarow["newmachine"] = dtNewMachinePlan.Select("ordertypename = '新机'").Length;
            datarow["examerepair"] = dtNewMachinePlan.Select("ordertypename = '检修'").Length;
            datarow["startdate"] = dtYearPlan.Rows.Count > 0 ? dtYearPlan.Rows[0]["startdate"] : "";
            datarow["finishdate"] = dtYearPlan.Rows.Count > 0 ? dtYearPlan.Rows[0]["finishdate"] : "";
            dtPlan.Rows.Add(datarow);

            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("type");//类型
            dtResult.Columns.Add("name");
            dtResult.Columns.Add("planQty");//计划数
            dtResult.Columns.Add("finishedQty");//完成数
            dtResult.Columns.Add("notFinishedQty");//未完成数

            DataRow dr1 = dtResult.NewRow();
            dr1["type"] = "pichan";
            dr1["name"] = "批产";
            dr1["planQty"] = dtPlan.Rows[0]["batchproduct"];
            dtResult.Rows.Add(dr1);

            DataRow dr2 = dtResult.NewRow();
            dr2["type"] = "xinji";
            dr2["name"] = "新机";
            dr2["planQty"] = dtPlan.Rows[0]["newmachine"];
            dtResult.Rows.Add(dr2);

            DataRow dr3 = dtResult.NewRow();
            dr3["type"] = "jianxiu";
            dr3["name"] = "检修";
            dr3["planQty"] = dtPlan.Rows[0]["examerepair"];
            dtResult.Rows.Add(dr3);

            DataRow dr4 = dtResult.NewRow();
            dr4["type"] = "beijian";
            dr4["name"] = "备件";
            dr4["planQty"] = dtPlan.Rows[0]["spareparts"];
            dtResult.Rows.Add(dr4);

            DataRow dr5 = dtResult.NewRow();
            dr5["type"] = "daxiu";
            dr5["name"] = "大修";
            dr5["planQty"] = dtPlan.Rows[0]["bigrepair"];
            dtResult.Rows.Add(dr5);

            //查询大修、备件完成数
            sql = @"
                    SELECT t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
                            to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate
                    FROM
                    (
                        SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
                               CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
                                    ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                                               WHEN ot.ordertypename = 'PD02' THEN '新机'
                                               WHEN ot.ordertypename = 'PD03' THEN '新机'
                                               WHEN ot.ordertypename = 'PD05' THEN '大修'
                                               WHEN ot.ordertypename = 'PD06' THEN '检修'
                                          END) 
                               END ordertypename
                        FROM enginepartplan ep
                        LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                        LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
                        LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
                        LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
                        WHERE TRUNC(mfg.factorycompletiondate) >= TRUNC(SYSDATE,'yyyy') 
                              AND trunc(mfg.factorycompletiondate) <= add_months(TRUNC(sysdate, 'yyyy'), 12) - 1
                              AND NVL(u2.attributevalue,ot.ordertypename) IN ('PD05','大修','备件')
                    ) t
                    HAVING COUNT(1) = SUM(t.usecompleted)
                    GROUP BY t.mfgordername,t.attributevalue,t.ordertypename";

            DataTable dtQueryData = dal.GetDataTable(sql);

            //查询批产完成数
            sql = @"
                    SELECT t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
                            to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate
                    FROM
                    (
                        SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
                               CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
                                    ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                                               WHEN ot.ordertypename = 'PD02' THEN '新机'
                                               WHEN ot.ordertypename = 'PD03' THEN '新机'
                                               WHEN ot.ordertypename = 'PD05' THEN '大修'
                                               WHEN ot.ordertypename = 'PD06' THEN '检修'
                                          END) 
                               END ordertypename
                        FROM enginepartplan ep
                        LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                        LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
                        LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
                        LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
                        LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                        WHERE TRUNC(mfg.factorycompletiondate) >= TRUNC(SYSDATE,'yyyy') 
                              AND trunc(mfg.factorycompletiondate) <= add_months(TRUNC(sysdate, 'yyyy'), 12) - 1
                              AND NVL(u2.attributevalue,ot.ordertypename) IN ('PD01','批产')
                              AND ep.type = 1 AND ws.workflowstepname = '0120-检验试车'
                    ) t
                    HAVING COUNT(1) = SUM(t.usecompleted)
                    GROUP BY t.mfgordername,t.attributevalue,t.ordertypename";

            DataTable dtPichanFinish = dal.GetDataTable(sql);


            //计算批产完成台数
            if (dtPichanFinish.Select(string.Format(" ordertypename = '批产' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                   dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length > 0)
            {
                dtResult.Rows[0]["finishedQty"] = dtPichanFinish.Select(string.Format(" ordertypename = '批产' AND finishdate >= #{0}# and finishdate <= #{1}#",
                    dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length;
            }
            else
            {
                dtResult.Rows[0]["finishedQty"] = 0;
            }

            //计算备件完成数
            if (dtQueryData.Select(string.Format(" ordertypename = '备件' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                                   dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length > 0)
            {
                dtResult.Rows[3]["finishedQty"] = dtQueryData.Select(string.Format(" ordertypename = '备件' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                                                                     dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length;
            }
            else
            {
                dtResult.Rows[3]["finishedQty"] = 0;
            }

            //计算大修完成台数
            if (dtQueryData.Select(string.Format(" ordertypename = '大修' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                                   dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length > 0)
            {
                dtResult.Rows[4]["finishedQty"] = dtQueryData.Select(string.Format(" ordertypename = '大修' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                                                                     dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length;
            }
            else
            {
                dtResult.Rows[4]["finishedQty"] = 0;
            }

            //查询新机、检修已关闭订单信息
            sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,mfg.factorycompletiondate,
       CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
            ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                       WHEN ot.ordertypename = 'PD02' THEN '新机'
                       WHEN ot.ordertypename = 'PD03' THEN '新机'
                       WHEN ot.ordertypename = 'PD05' THEN '大修'
                       WHEN ot.ordertypename = 'PD06' THEN '检修'
                  END) 
       END ordertypename,ml.finishdate
FROM mfgorder mfg
LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
LEFT JOIN (
             SELECT ml.mfgorderid,MAX(ml.operatetime) finishdate
             FROM mfgorderoperatelog ml
             GROUP BY ml.mfgorderid
          ) ml ON ml.mfgorderid = mfg.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE) AND od.orderstatusname='关闭'
";

            DataTable dtMfgClosed = dal.GetDataTable(sql);

            //计算【新机】完成台数（订单关闭视作为已完成）
            if (dtMfgClosed.Select(string.Format(" ordertypename = '新机' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                   dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length > 0)
            {
                dtResult.Rows[1]["finishedQty"] = dtMfgClosed.Select(string.Format(" ordertypename = '新机' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                                                                     dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length;
            }
            else
            {
                dtResult.Rows[1]["finishedQty"] = 0;
            }

            //计算【检修】完成台数（订单关闭视作为已完成）
            if (dtMfgClosed.Select(string.Format(" ordertypename = '检修' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                   dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length > 0)
            {
                dtResult.Rows[2]["finishedQty"] = dtMfgClosed.Select(string.Format(" ordertypename = '检修' AND finishdate >= #{0}# and finishdate <= #{1}#",
                                                                                     dtPlan.Rows[0]["startdate"].ToString(), dtPlan.Rows[0]["finishdate"].ToString())).Length;
            }
            else
            {
                dtResult.Rows[2]["finishedQty"] = 0;
            }

            foreach (DataRow dr in dtResult.Rows)
            {
                //没有计划或计划台数为0时，默认完成数为1,未完成数为0,这样计算出来的完成率为100%;
                if (dr["planQty"].ToString() == "0")
                {
                    dr["finishedQty"] = 0;
                    dr["notFinishedQty"] = 0;
                }

                //计划台数不为0且完成数>计划数时，设置未完成数为0，这样计算出的完成率不会大于100%
                if ( (dr["planQty"].ToString() != "0") && Convert.ToDecimal(dr["finishedQty"]) > Convert.ToDecimal(dr["planQty"]))
                {
                    dr["notFinishedQty"] = 0;
                }

                if ((dr["planQty"].ToString() != "0") && Convert.ToDecimal(dr["finishedQty"]) <= Convert.ToDecimal(dr["planQty"]))
                {
                    dr["notFinishedQty"] = Convert.ToDecimal(dr["planQty"]) - Convert.ToDecimal(dr["finishedQty"]);
                }
            }

            return dtResult;
        }

        //年生产任务完成明细
        public DataTable GetZsYearWorkingFinish(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
                        SELECT nvl(yp.batchproduct,0) batchproduct,nvl(yp.newmachine,0) newmachine,nvl(yp.bigrepair,0) bigrepair,
                               nvl(yp.examerepair,0) examerepair,nvl(yp.spareparts,0) spareparts,yp.startdate,yp.finishdate
                        FROM zsyearplanproduce yp WHERE yp.planyear = to_char(SYSDATE,'yyyy')";

            DataTable dtPlan = dal.GetDataTable(sql);

            if (dtPlan.Rows.Count == 0)
            {
                pageCount = 0;
                rowCount = 0;
                return new DataTable();
            }

            if (para["queryType"].ToString() == "新机" || para["queryType"].ToString() == "检修")
            {
                sql = @"
                        SELECT t2.mfgorderid,t2.mfgordername,t2.attributevalue,t2.ordertypename,t2.finishdate
                        FROM
                        (
                          SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,
                                 CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
                                      ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                                                 WHEN ot.ordertypename = 'PD02' THEN '新机'
                                                 WHEN ot.ordertypename = 'PD03' THEN '新机'
                                                 WHEN ot.ordertypename = 'PD05' THEN '大修'
                                                 WHEN ot.ordertypename = 'PD06' THEN '检修'
                                            END) 
                                 END ordertypename,ml.finishdate
                          FROM mfgorder mfg
                          LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
                          LEFT JOIN (
                                       SELECT ml.mfgorderid,MAX(ml.operatetime) finishdate
                                       FROM mfgorderoperatelog ml
                                       GROUP BY ml.mfgorderid
                                    ) ml ON ml.mfgorderid = mfg.mfgorderid
                          LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
                          LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
                          LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
                          WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE) AND od.orderstatusname='关闭'
                        ) t2
                        WHERE 1=1 ";
            }
            else if (para["queryType"].ToString() == "批产")
            {
                sql = @"SELECT t2.mfgordername,t2.attributevalue,t2.ordertypename,t2.finishdate
                        FROM
                        (
                          SELECT t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
                                 to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate
                          FROM
                          (
                              SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
                                     CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
                                          ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                                                     WHEN ot.ordertypename = 'PD02' THEN '新机'
                                                     WHEN ot.ordertypename = 'PD03' THEN '新机'
                                                     WHEN ot.ordertypename = 'PD05' THEN '大修'
                                                     WHEN ot.ordertypename = 'PD06' THEN '检修'
                                                END) 
                                     END ordertypename
                              FROM enginepartplan ep
                              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                              LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
                              LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
                              LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
                              LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                              WHERE trunc(mfg.factorycompletiondate) >= TRUNC(SYSDATE,'yyyy') 
                                    AND trunc(mfg.factorycompletiondate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1 
                                    AND NVL(u2.attributevalue,ot.ordertypename) IN ('PD01','批产')
                                    AND ep.type = 1 AND ws.workflowstepname = '0120-检验试车'
                          ) t
                          HAVING COUNT(1) = SUM(t.usecompleted)
                          GROUP BY t.mfgordername,t.attributevalue,t.ordertypename
                        ) t2
                        WHERE 1=1 ";
            }
            else //大修、备件
            {
                sql = @"SELECT t2.mfgordername,t2.attributevalue,t2.ordertypename,t2.finishdate
                        FROM
                        (
                          SELECT t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
                                 to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate
                          FROM
                          (
                              SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
                                     CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
                                          ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                                                     WHEN ot.ordertypename = 'PD02' THEN '新机'
                                                     WHEN ot.ordertypename = 'PD03' THEN '新机'
                                                     WHEN ot.ordertypename = 'PD05' THEN '大修'
                                                     WHEN ot.ordertypename = 'PD06' THEN '检修'
                                                END) 
                                     END ordertypename
                              FROM enginepartplan ep
                              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                              LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
                              LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
                              LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
                              WHERE trunc(mfg.factorycompletiondate) >= TRUNC(SYSDATE,'yyyy') 
                                    AND trunc(mfg.factorycompletiondate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1 
                                    AND NVL(u2.attributevalue,ot.ordertypename) IN ('PD05','大修','备件')
                          ) t
                          HAVING COUNT(1) = SUM(t.usecompleted)
                          GROUP BY t.mfgordername,t.attributevalue,t.ordertypename
                        ) t2
                        WHERE 1=1 ";
            }

            sql += string.Format(" AND t2.ordertypename = '{0}' ", para["queryType"].ToString());
            sql += string.Format(" AND TRUNC(t2.finishdate) >= TRUNC(to_date('{0}','yyyy/mm/dd hh24:mi:ss'))", dtPlan.Rows[0]["startdate"].ToString());
            sql += string.Format(" AND TRUNC(t2.finishdate) <= TRUNC(to_date('{0}','yyyy/mm/dd hh24:mi:ss'))", dtPlan.Rows[0]["finishdate"].ToString());

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            return retR.DBTable;
        }

        //修理、备件本月进度
        public DataTable GetXiuliOrBeiJianData()
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("type");
            dtResult.Columns.Add("planQty");//计划数
            dtResult.Columns.Add("finishedQty");//完成数
            dtResult.Columns.Add("toDelayQty");//预超期数
            dtResult.Columns.Add("delayQty");//超期数

            for (int i = 0; i < 3; i++)
            {
                DataRow dr = dtResult.NewRow();
                dr["planQty"] = 0;
                dr["finishedQty"] = 0;
                dr["toDelayQty"] = 0;
                dr["delayQty"] = 0;
                dtResult.Rows.Add(dr);
            }

            dtResult.Rows[0]["type"] = "大修";
            dtResult.Rows[1]["type"] = "检修";
            dtResult.Rows[2]["type"] = "备件";


            string sql = @"
SELECT t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
        t.factorycompletiondate,to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd')-TRUNC(SYSDATE) todelaydays,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd') delaydays
FROM
(
SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25) --上月26号
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )
     
UNION ALL 

SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )

UNION ALL

SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE u.attributevalue IN 
       (
           SELECT u.attributevalue
           FROM enginepartplan ep
           LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
           LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
           GROUP BY u.attributevalue
           HAVING COUNT(ep.usecompleted) = SUM(ep.usecompleted) AND MAX(ep.finishdate) BETWEEN (trunc(add_months(sysdate,-1),'mm') + 25) AND (trunc(sysdate,'mm') + 24)
       )
) t
WHERE t.ordertypename IN ('大修','检修','备件')
GROUP BY t.mfgordername,t.attributevalue,t.ordertypename,t.factorycompletiondate
";
            DataTable dtQueryData = dal.GetDataTable(sql);

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("ordertypename='{0}'",dr["type"]);
                dr["planQty"] = dtQueryData.Select(filter).Length;
                //完成数：发动机任务单数=已完工任务单数
                dr["finishedQty"] = dtQueryData.Select(string.Format("ordertypename = '{0}' and qty = finishqty", dr["type"])).Length;

                //预超期数：发动机任务单数>已完工任务单数 && 当前时间和计划完成时间相距0或1天
                dr["toDelayQty"] = dtQueryData.Select(string.Format("ordertypename = '{0}' and qty > finishqty and todelaydays > -1 and todelaydays < 4 ", dr["type"])).Length;

                //超期数：(1)+(2)
                //(1):发动机任务单数>已完工任务单数 && 当前时间 > 要求完成时间
                //(2):发动机任务单数=已完工任务单数 && 完成时间 > 要求完成时间
                dr["delayQty"] = dtQueryData.Select(string.Format("ordertypename = '{0}' and qty > finishqty and todelaydays <= -1 ", dr["type"])).Length
                    + dtQueryData.Select(string.Format("ordertypename = '{0}' and qty = finishqty and delaydays > 0 ", dr["type"])).Length;
            }

            return dtResult;
        }

        //修理、备件本月进度明细
        public DataTable GetXiuliOrBeiJianDetailsData(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT n.*
FROM
(

SELECT t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
        t.factorycompletiondate,t.factorystardate,to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd')-TRUNC(SYSDATE) todelaydays,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd') delaydays
FROM
(
SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25) --上月26号
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )
     
UNION ALL 

SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )

UNION ALL

SELECT mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE u.attributevalue IN 
       (
           SELECT u.attributevalue
           FROM enginepartplan ep
           LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
           LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
           GROUP BY u.attributevalue
           HAVING COUNT(ep.usecompleted) = SUM(ep.usecompleted) AND MAX(ep.finishdate) BETWEEN (trunc(add_months(sysdate,-1),'mm') + 25) AND (trunc(sysdate,'mm') + 24)
       )
) t
WHERE t.ordertypename IN ('大修','检修','备件')
GROUP BY t.mfgordername,t.attributevalue,t.ordertypename,t.factorycompletiondate,t.factorystardate
) n
WHERE 1=1 ";
            if (para.ContainsKey("ordertypename"))
            {
                sql += string.Format(" AND n.ordertypename = '{0}' ", para["ordertypename"].ToString());
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "超期数")
            {
                sql += " AND n.todelaydays <= -1";
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        public DataTable GetPiChanDataOld()
        {
            string sql = @"
SELECT  pf.productfamilyname,t.mfgorderid,t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
        t.factorycompletiondate,to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd')-TRUNC(SYSDATE) todelaydays,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd') delaydays
FROM
(
SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25) --上月26号
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )
     
UNION ALL 

SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )

UNION ALL

SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE u.attributevalue IN 
       (
           SELECT u.attributevalue
           FROM enginepartplan ep
           LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
           LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
           GROUP BY u.attributevalue
           HAVING COUNT(ep.usecompleted) = SUM(ep.usecompleted) AND MAX(ep.finishdate) BETWEEN (trunc(add_months(sysdate,-1),'mm') + 25) AND (trunc(sysdate,'mm') + 24)
       )
) t
LEFT JOIN
(
      SELECT mfg.mfgorderid,pf.productfamilyname
      FROM mfgorder mfg
      LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
      LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
      LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
      LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
) pf ON pf.mfgorderid = t.mfgorderid
WHERE t.ordertypename = '批产'
GROUP BY pf.productfamilyname,t.mfgorderid,t.mfgordername,t.attributevalue,t.ordertypename,t.factorycompletiondate
";
            DataTable dtQueryData = dal.GetDataTable(sql);

            DataTable dtResult = dtQueryData.DefaultView.ToTable(true, "productfamilyname");
            dtResult.Columns.Add("planQty");//计划数
            dtResult.Columns.Add("finishedQty");//完成数
            dtResult.Columns.Add("toDelayQty");//预超期数
            dtResult.Columns.Add("delayQty");//超期数

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("productfamilyname='{0}'", dr["productfamilyname"]);
                dr["planQty"] = dtQueryData.Select(filter).Length;
                //完成数：发动机任务单数=已完工任务单数
                dr["finishedQty"] = dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty = finishqty", dr["productfamilyname"])).Length;

                //预超期数：发动机任务单数>已完工任务单数 && 当前时间和计划完成时间相距0或1天
                dr["toDelayQty"] = dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty > finishqty and todelaydays > -1 and todelaydays < 2 ", dr["productfamilyname"])).Length;

                //超期数：(1)+(2)
                //(1):发动机任务单数>已完工任务单数 && 当前时间 > 要求完成时间
                //(2):发动机任务单数=已完工任务单数 && 完成时间 > 要求完成时间
                dr["delayQty"] = dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty > finishqty and todelaydays <= -1 ", dr["productfamilyname"])).Length
                    + dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty = finishqty and delaydays > 0 ", dr["productfamilyname"])).Length;
            }

            return dtResult;
        }

        public DataTable GetPiChanDetailsDataOld(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT n.productfamilyname,n.mfgordername,n.attributevalue,n.ordertypename,n.qty,n.finishqty,n.finishdate,n.factorycompletiondate,n.factorystardate,n.todelaydays,
       CASE WHEN n.qty > n.finishqty THEN TRUNC(SYSDATE - n.factorycompletiondate) WHEN n.qty <= n.finishqty THEN TRUNC(n.finishdate - n.factorycompletiondate ) END delaydays
FROM
(
SELECT  pf.productfamilyname,t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
        t.factorycompletiondate,t.factorystardate,to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd')-TRUNC(SYSDATE) todelaydays,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd') delaydays
FROM
(
SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25) --上月26号
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )
    
UNION ALL 

SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE 
     mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )

UNION ALL

SELECT mfg.mfgorderid,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,mfg.factorystardate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
WHERE u.attributevalue IN 
       (
           SELECT u.attributevalue
           FROM enginepartplan ep
           LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
           LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
           GROUP BY u.attributevalue
           HAVING COUNT(ep.usecompleted) = SUM(ep.usecompleted) AND MAX(ep.finishdate) BETWEEN (trunc(add_months(sysdate,-1),'mm') + 25) AND (trunc(sysdate,'mm') + 24)
       )
) t
LEFT JOIN (
                SELECT mfg.mfgorderid,pf.productfamilyname
                FROM mfgorder mfg
                LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
                LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
                LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
                LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
          ) pf ON pf.mfgorderid = t.mfgorderid
WHERE t.ordertypename = '批产'
GROUP BY pf.productfamilyname,t.mfgorderid,t.mfgordername,t.attributevalue,t.ordertypename,t.factorycompletiondate,t.factorystardate
) n
WHERE 1=1 ";
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND n.productfamilyname = '{0}' ", para["productfamilyname"].ToString());
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "超期数")
            {
                sql += " AND ((n.qty > n.finishqty AND n.todelaydays <= -1) OR (n.qty = n.finishqty AND n.delaydays > 0))";
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //批产本月进度 add zhunan 2019.12.3
        public DataTable GetPiChanData()
        {
            string sql = @"
SELECT t.mfgorderid,t.attributevalue,t.factorycompletiondate,MAX(t.usecompleted) usecompleted,
       t.ordertypename,pf.productfamilyname,to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate,
       CASE WHEN MAX(t.usecompleted)= 1 
            THEN to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd')
            WHEN MAX(t.usecompleted)= 0 
            THEN TRUNC(SYSDATE) - to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd') 
       END delaydays
FROM
(
    --上月任务当前未完成
    SELECT mfg.mfgorderid,u.attributevalue,mfg.factorycompletiondate,
           ep.usecompleted,ep.finishdate,
           CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue 
                ELSE DECODE(ot.ordertypename,'PD01','批产','其他')
           END ordertypename
    FROM enginepartplan ep
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    WHERE mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25)
          AND ep.workflowstepid = ep.completestepid
          AND p.productid = ep.productid AND ep.usecompleted = 0
          
    UNION ALL 

    --本月任务当前未完成
    SELECT mfg.mfgorderid,u.attributevalue,mfg.factorycompletiondate,
           ep.usecompleted,ep.finishdate,
           CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue 
                ELSE DECODE(ot.ordertypename,'PD01','批产','其他')
           END ordertypename
    FROM enginepartplan ep
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    WHERE mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
          AND ep.workflowstepid = ep.completestepid
          AND p.productid = ep.productid AND ep.usecompleted = 0
          
    UNION ALL 

    --在本月完成的所有任务
    SELECT mfg.mfgorderid,u.attributevalue,mfg.factorycompletiondate,
           ep.usecompleted,ep.finishdate,
           CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue 
                ELSE DECODE(ot.ordertypename,'PD01','批产','其他')
           END ordertypename
    FROM enginepartplan ep
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    WHERE ep.workflowstepid = ep.completestepid
          AND p.productid = ep.productid AND ep.usecompleted = 1
          AND ep.finishdate BETWEEN (trunc(add_months(sysdate,-1),'mm') + 25) AND (trunc(sysdate,'mm') + 24) --在本月完成任务
) t
LEFT JOIN
(
      SELECT mfg.mfgorderid,pf.productfamilyname
      FROM mfgorder mfg
      LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
      LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
      LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
      LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
) pf ON pf.mfgorderid = t.mfgorderid
WHERE t.ordertypename = '批产'
GROUP BY t.mfgorderid,t.attributevalue,t.factorycompletiondate,t.ordertypename,pf.productfamilyname
";
            DataTable dtQueryData = dal.GetDataTable(sql);

            DataTable dtResult = dtQueryData.DefaultView.ToTable(true, "productfamilyname");
            dtResult.Columns.Add("planQty");//计划数
            dtResult.Columns.Add("delayQty");//超期数

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("productfamilyname='{0}'", dr["productfamilyname"]);
                //计划数
                dr["planQty"] = dtQueryData.Select(filter).Length;

                //超期数：(1)+(2)
                //(1):所指定的大阶段未完工 && 当前时间 > 要求完成时间
                //(2):所指定的大阶段已完工 && 完成时间 > 要求完成时间
                dr["delayQty"] = dtQueryData.Select(string.Format("productfamilyname = '{0}' and usecompleted = 0 and delaydays > 0 ", dr["productfamilyname"])).Length
                    + dtQueryData.Select(string.Format("productfamilyname = '{0}' and usecompleted = 1 and delaydays > 0", dr["productfamilyname"])).Length;
            }

            return dtResult;
        }

        //批产本月进度明细 add zhunan 2019.12.3
        public DataTable GetPiChanDetailsData(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT *
FROM
(
SELECT t.mfgorderid,t.attributevalue,t.factorycompletiondate,t.factorystardate,MAX(t.usecompleted) usecompleted,
       t.ordertypename,pf.productfamilyname,TRUNC(MAX(t.finishdate)) finishdate,
       CASE WHEN MAX(t.usecompleted)= 1 
            THEN TRUNC(MAX(t.finishdate)) - TRUNC(t.factorycompletiondate)
            WHEN MAX(t.usecompleted)= 0 
            THEN TRUNC(SYSDATE) - TRUNC(t.factorycompletiondate) 
       END delaydays
FROM
(
    SELECT mfg.mfgorderid,u.attributevalue,mfg.factorycompletiondate,mfg.factorystardate,
           ep.usecompleted,ep.finishdate,
           CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue 
                ELSE DECODE(ot.ordertypename,'PD01','批产','其他')
           END ordertypename
    FROM enginepartplan ep
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    WHERE mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25)
          AND ep.workflowstepid = ep.completestepid
          AND p.productid = ep.productid AND ep.usecompleted = 0
          
    UNION ALL 

    SELECT mfg.mfgorderid,u.attributevalue,mfg.factorycompletiondate,mfg.factorystardate,
           ep.usecompleted,ep.finishdate,
           CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue 
                ELSE DECODE(ot.ordertypename,'PD01','批产','其他')
           END ordertypename
    FROM enginepartplan ep
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    WHERE mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
          AND ep.workflowstepid = ep.completestepid
          AND p.productid = ep.productid AND ep.usecompleted = 0
          
    UNION ALL 

    SELECT mfg.mfgorderid,u.attributevalue,mfg.factorycompletiondate,mfg.factorystardate,
           ep.usecompleted,ep.finishdate,
           CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue 
                ELSE DECODE(ot.ordertypename,'PD01','批产','其他')
           END ordertypename
    FROM enginepartplan ep
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    WHERE ep.workflowstepid = ep.completestepid
          AND p.productid = ep.productid AND ep.usecompleted = 1
          AND ep.finishdate BETWEEN (trunc(add_months(sysdate,-1),'mm') + 25) AND (trunc(sysdate,'mm') + 24)
) t
LEFT JOIN
(
      SELECT mfg.mfgorderid,pf.productfamilyname
      FROM mfgorder mfg
      LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
      LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
      LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
      LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
) pf ON pf.mfgorderid = t.mfgorderid
WHERE t.ordertypename = '批产'
GROUP BY t.mfgorderid,t.attributevalue,t.factorycompletiondate,t.factorystardate,t.ordertypename,pf.productfamilyname
) M
WHERE 1=1 
";
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND M.productfamilyname = '{0}' ", para["productfamilyname"].ToString());
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "超期数")
            {
                sql += " AND M.delaydays > 0 ";
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //新机本月进度
        public DataTable GetXinJiData()
        {
            string sql = @"
SELECT  t.productfamilyname,t.mfgordername,t.attributevalue,t.ordertypename,COUNT(1) qty,SUM(t.usecompleted) finishqty,
        t.factorycompletiondate,to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd')-TRUNC(SYSDATE) todelaydays,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') finishdate,
        to_date(to_char(MAX(t.finishdate),'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(t.factorycompletiondate,'yyyy/mm/dd'),'yyyy/mm/dd') delaydays
FROM
(
SELECT pf.productfamilyname,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
WHERE 
     mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25) --上月26号
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate < (trunc(add_months(sysdate,-1),'mm') + 25)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )
     
UNION ALL 

SELECT pf.productfamilyname,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
WHERE 
     mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
     AND u.attributevalue IN 
     (
         SELECT u.attributevalue
         FROM enginepartplan ep
         LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
         LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
         WHERE mfg.factorycompletiondate >= (trunc(add_months(sysdate,-1),'mm') + 25) AND mfg.factorycompletiondate <= (trunc(sysdate,'mm') + 24)
         GROUP BY u.attributevalue
         HAVING COUNT(ep.usecompleted) > SUM(ep.usecompleted)
     )

UNION ALL

SELECT pf.productfamilyname,mfg.mfgordername,u.attributevalue,ep.usecompleted,ep.finishdate,mfg.factorycompletiondate,ep.containerno,
        CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修' END
                  ) 
        END ordertypename
FROM enginepartplan ep
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
WHERE u.attributevalue IN 
       (
           SELECT u.attributevalue
           FROM enginepartplan ep
           LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
           LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
           GROUP BY u.attributevalue
           HAVING COUNT(ep.usecompleted) = SUM(ep.usecompleted) AND MAX(ep.finishdate) BETWEEN (trunc(add_months(sysdate,-1),'mm') + 25) AND (trunc(sysdate,'mm') + 24)
       )
) t
WHERE t.ordertypename = '新机'
GROUP BY t.productfamilyname,t.mfgordername,t.attributevalue,t.ordertypename,t.factorycompletiondate
";
            DataTable dtQueryData = dal.GetDataTable(sql);

            DataTable dtResult = dtQueryData.DefaultView.ToTable(true, "productfamilyname");
            dtResult.Columns.Add("planQty");//计划数
            dtResult.Columns.Add("finishedQty");//完成数
            dtResult.Columns.Add("toDelayQty");//预超期数
            dtResult.Columns.Add("delayQty");//超期数

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("productfamilyname='{0}'", dr["productfamilyname"]);
                dr["planQty"] = dtQueryData.Select(filter).Length;
                dr["finishedQty"] = dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty = finishqty", dr["productfamilyname"])).Length;
                dr["toDelayQty"] = dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty > finishqty and todelaydays > -1 and todelaydays < 4 ", dr["productfamilyname"])).Length;
                dr["delayQty"] = dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty > finishqty and todelaydays <= -1 ", dr["productfamilyname"])).Length
                    + dtQueryData.Select(string.Format("productfamilyname = '{0}' and qty = finishqty and delaydays > 0 ", dr["productfamilyname"])).Length;
            }

            return dtResult;
        }

        //返件本月进度
        public DataTable GetFanJianData()
        {
            string sql = @"
                         SELECT db.enginefamily,db.engineno,dbt.RequireFinishTime needFinishDate,dbt.recvFactoryReturnTime returnDate,
                                DECODE(db.transferstatus,0,'',1,'',2,'',3,'返件返出',4,'返件接收',5,'返件完成',6,'返件返回',7,'已领回') status,
                                TRUNC(SYSDATE) - dbt.RequireFinishTime toDelayDays,
                                to_date(to_char(dbt.recvFactoryReturnTime,'yyyy/mm/dd'),'yyyy/mm/dd') - dbt.RequireFinishTime delayDays
                         FROM DealFaultBill db
                         LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid 
                         WHERE dbt.RequireFinishTime IS NOT NULL AND  db.transferstatus IN ('3','4','5')
 
                         UNION ALL
 
                         SELECT db.enginefamily,db.engineno,dbt.RequireFinishTime needFinishDate,dbt.recvFactoryReturnTime returnDate,
                                DECODE(db.transferstatus,0,'',1,'',2,'',3,'返件返出',4,'返件接收',5,'返件完成',6,'返件返回',7,'已领回') status,
                                TRUNC(SYSDATE) - dbt.RequireFinishTime toDelayDays,
                                to_date(to_char(dbt.recvFactoryReturnTime,'yyyy/mm/dd'),'yyyy/mm/dd') - dbt.RequireFinishTime delayDays
                         FROM DealFaultBill db
                         LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
                         WHERE dbt.RequireFinishTime IS NOT NULL AND  db.transferstatus IN ('6','7') AND dbt.recvFactoryReturnTime IS NOT NULL 
                               AND to_date(to_char(dbt.recvFactoryReturnTime,'yyyy/mm/dd'),'yyyy/mm/dd') BETWEEN TRUNC(SYSDATE,'mm') AND trunc(last_day(sysdate)) ";

            DataTable dtQueryData = dal.GetDataTable(sql);

            DataTable dtResult = dtQueryData.DefaultView.ToTable(true, "enginefamily");
            dtResult.Columns.Add("planQty");//总数
            dtResult.Columns.Add("finishedQty");//完成数
            dtResult.Columns.Add("toDelayQty");//预超期数
            dtResult.Columns.Add("delayQty");//超期数

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("enginefamily='{0}'", dr["enginefamily"]);
                dr["planQty"] = dtQueryData.Select(filter).Length;
                dr["finishedQty"] = dtQueryData.Select(string.Format("enginefamily = '{0}' AND returnDate IS NOT NULL ", dr["enginefamily"])).Length;
                dr["toDelayQty"] = dtQueryData.Select(string.Format("enginefamily = '{0}' AND returnDate IS NOT NULL AND toDelayDays >-1 AND toDelayDays < 2", dr["enginefamily"])).Length;
                dr["delayQty"] = dtQueryData.Select(string.Format("enginefamily = '{0}' AND returnDate IS NOT NULL AND delayDays > 1 ", dr["enginefamily"])).Length
                    + dtQueryData.Select(string.Format("enginefamily = '{0}' AND returnDate IS NULL AND  toDelayDays < 0 ", dr["enginefamily"])).Length;
            }
            return dtResult;
        }

        //新机各工序进度
        public DataTable GetXinjiStepInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            //查询未全部完工的新机发动机列表
            string sql = @"
SELECT t.engineno
FROM 
(
  SELECT zp.engineno,SUM(DECODE(zp.factfinisheddate,NULL,0,1)) finished,COUNT(1) qty
  FROM zsstepdispatchplan zp
  LEFT JOIN workflowstep ws ON ws.workflowstepid = zp.workflowstepid
  WHERE ws.workflowstepname IS NOT NULL
  GROUP BY zp.engineno
) t 
WHERE t.finished <> t.qty
";
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            DataTable dtEngineList = retR.DBTable;

            if (dtEngineList.Rows.Count == 0)
            {
                return new DataTable();
            }

            StringBuilder sb = new StringBuilder();
            sb.Append("(");
            foreach (DataRow dr in dtEngineList.Rows)
            {
                sb.AppendFormat("'{0}',",dr["engineno"].ToString());
            }
            sb.Remove(sb.ToString().Length - 1, 1);
            sb.Append(")");


            sql = @"
SELECT t.sequence,t.workflowstepname,t.engineno,CASE WHEN SUM(t.isfinished) = COUNT(1) THEN '完成' ELSE '未完成' END status
FROM
(
  SELECT ws.sequence,ws.workflowstepname,zp.engineno,CASE WHEN zp.factfinisheddate IS NOT NULL THEN 1 ELSE 0 END isfinished
  FROM zsstepdispatchplan zp
  LEFT JOIN Workflowstep ws ON ws.workflowstepid = zp.workflowstepid
  WHERE ws.workflowstepname IS NOT NULL AND zp.engineno IN ";

            sql += sb.ToString();

            sql += @"
) t 
GROUP BY t.sequence,t.workflowstepname,t.engineno
ORDER BY t.engineno,t.sequence
";
            DataTable dtSpec = dal.GetDataTable(sql);

            DataTable dtResult = dtSpec.Clone();

            foreach (DataRow dr in dtEngineList.Rows)
            {
                string filter = string.Format("engineno = '{0}'", dr["engineno"].ToString());
                DataRow[] drSpec = dtSpec.Select(filter);

                //工序数大于等于9，只添加前9道工序
                if (drSpec.Length >= 9)
                {
                    for (int i = 0; i < 9; i++)
                    {
                        DataRow datarow = dtResult.NewRow();
                        datarow["sequence"] = drSpec[i]["sequence"];
                        datarow["workflowstepname"] = drSpec[i]["workflowstepname"];
                        datarow["engineno"] = drSpec[i]["engineno"];
                        datarow["status"] = drSpec[i]["status"];
                        dtResult.Rows.Add(datarow);
                    }
                }

                //工序数不够9道序
                if (drSpec.Length < 9)
                {
                    //先添加所有工序
                    for (int i = 0; i < drSpec.Length; i++)
                    {
                        DataRow datarow = dtResult.NewRow();
                        datarow["sequence"] = drSpec[i]["sequence"];
                        datarow["workflowstepname"] = drSpec[i]["workflowstepname"];
                        datarow["engineno"] = drSpec[i]["engineno"];
                        datarow["status"] = drSpec[i]["status"];
                        dtResult.Rows.Add(datarow);
                    }

                    //凑够9道工序
                    for (int i = 0; i < (9-drSpec.Length); i++)
                    {
                        DataRow datarow = dtResult.NewRow();
                        datarow["sequence"] = drSpec.Length + (i +1);
                        datarow["workflowstepname"] = "";
                        datarow["engineno"] = dr["engineno"];
                        datarow["status"] = "";
                        dtResult.Rows.Add(datarow);
                    }
                }
            }

            return dtResult;
        }

        //新机各工序进度--明细
        public DataTable GetXinjiStepDetailsInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT zs.engineno,ws.sequence,ws.workflowstepname,st.stepno,nvl(st.stepno,1) I,zs.stepinfodesc,
       to_char(zs.plannedstartdate,'yyyy/mm/dd') 开始时间,to_char(zs.plannedfinishdate,'yyyy/mm/dd') 结束时间,
       TRUNC(zs.plannedfinishdate - zs.plannedstartdate) + 1 || '天' 排产周期,
       CASE WHEN zs.factfinisheddate IS NOT NULL THEN to_char(zs.factfinisheddate,'yyyy/mm/dd')
            WHEN zs.factfinisheddate IS NULL AND TRUNC(SYSDATE) < TRUNC(zs.plannedstartdate) 
              THEN '未完成' || '(' || (TRUNC(zs.plannedfinishdate - zs.plannedstartdate) + 1 || '天') || ')'
            WHEN zs.factfinisheddate IS NULL AND TRUNC(SYSDATE) >= TRUNC(zs.plannedstartdate) AND TRUNC(SYSDATE) <= TRUNC(zs.plannedfinishdate)
              THEN '未完成' || '(' || (zs.plannedfinishdate - TRUNC(SYSDATE) + 1 || '天') || ')' 
            WHEN zs.factfinisheddate IS NULL AND TRUNC(SYSDATE) > TRUNC(zs.plannedfinishdate) THEN '未完成(已超期)'
       END 完成日期,
       zs.exceednotes,t.stepCount
FROM zsstepdispatchplan zs
LEFT JOIN Workflowstep ws ON ws.workflowstepid = zs.workflowstepid
LEFT JOIN stepinfo st ON st.stepinfoid = zs.stepinfoid
LEFT JOIN 
(
     SELECT zs.engineno,zs.workflowstepid,COUNT(1) stepCount
     FROM zsstepdispatchplan zs
     GROUP BY zs.engineno,zs.workflowstepid
) t ON t.engineno = zs.engineno AND t.workflowstepid = zs.workflowstepid
WHERE ws.workflowstepname IS NOT NULL ";
            if (para.ContainsKey("engineno"))
            {
                sql += string.Format(" AND zs.engineno = '{0}' ", para["engineno"].ToString());
            }
            
            sql += "ORDER BY zs.engineno,ws.sequence,st.stepno";

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }
    }
}
